
clear all

// This file creates a county-district-level dataset for the upper and lower houses of the state legislature using precinct-level elections data

// The data includes only the election years used in the final study
// State-specific corrections are noted in the Online Appendix

// Precinct-level election results from Harvard Dataverse (Ansolobehere et al.)
// Accessed here: https://projects.iq.harvard.edu/eda/data
// saved in a folder Data/input/Precinct Election results

// Download yearly data by state

tempfile csldu
tempfile csldl
save `csldl', emptyok
save `csldu', emptyok
tempfile st
save `st', emptyok


// ALABAMA
foreach i in 10 {

//state elections for 2010 only in AL
import delim "$InputPath/Precinct election results/AL_20`i'.tab", clear 
replace state="Alabama"
rename (sd ld fips_cnty) (sldu sldl fcounty)

***state specific***
drop if precinct=="ABSENTEE" | precinct=="PROVISIONAL" //reported at county levels

sort county precinct
replace precinct=precinct+"2" if precinct[_n]==precinct[_n-1] 
replace sldl=84 if fcounty==5 //typo
replace sldl=8 if fcounty==103 & sldl==5 //typo
replace ld2=8 if fcounty==103 & ld2==5 //same typo as above
replace sldl=46 if county=="JEFFERSON" & sldl==.    //new info
replace sldl=11 if county=="BLOUNT" & precinct=="BROOKSVILLE" //new info
********************

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape
save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl==. & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty county sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu==. & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state county fcounty sldu)

append using `csldu'
save `csldu', replace
}

// ALASKA

foreach i in 04 10 {

import delim "$InputPath/Precinct election results/AK_20`i'.tab", clear 
replace state="Alaska"
//rename (sd ld fips_cnty) (sldu sldl fcounty)

***state-specific***
if year==2002 {
drop precinct_code
rename ed_precinct precinct_code
egen g2002_sth_tv=rowtotal(g2002_sth_rv g2002_sth_dv), missing
egen g2002_sts_tv=rowtotal(g2002_sts_rv g2002_sts_dv), missing
}
gen sldl=substr(precinct_code,1,2)
destring sldl, replace force
gen sldu=.
gen fcounty=.
do "$CodePath/elections_csld_edits/districts_AK.do" //add fips county codes using precinct names
********************

save `st', replace

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace

}

// CALIFORNIA 
foreach i in 04 10 {

import delim "$InputPath/Precinct election results/CA_20`i'.tab", clear 
replace state="California"
rename (county_code sd ld) (fcounty sldu sldl)

***state-specific***
replace fcounty=fcounty-6000 //remove state fips prefix
drop if sldl==0 | sldu==0 //Drop county totals (where sldu=0 and sldl=0)

replace sldu=40 if fcounty==25 //new info
replace sldl=80 if fcounty==25 //new info

replace sldu=1 if fcounty==5 //new info
replace sldl=10 if fcounty==5 //new info

replace sldl=1 if fcounty==33 //missing in 2008 only
replace sldu=2 if fcounty==33 //missing in 2008 only

********************

save `st', replace

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}


// DELAWARE
foreach i in 04 10 {

import delim "$InputPath/Precinct election results/DE_20`i'.tab", clear 
replace state="Delaware"
rename (sd ld) (sldu sldl)

***state specific***
do "$CodePath/elections_csld_edits/districts_DE.do" //add fips county codes
gen county=fcounty
replace sldl=31 if precinct=="16-31"
replace sldl=41 if precinct=="16-41"
replace sldl=2 if precinct=="17-02"
********************

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape
save `st', replace


reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl==. & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu==. & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}

// FLORIDA
foreach i in 10 {

//FL only has state election data in 2010
import delim "$InputPath/Precinct election results/FL_20`i'.tab", clear 
replace state="Florida"
rename (sd ld) (sldu sldl)

***state specific***
do "$CodePath/elections_csld_edits/districts_FL.do" //add fips county codes
drop if sldl==. //this drops 40 reports (all turnout variables equal zero)
********************

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape
save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl==. & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu==. & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}



// IOWA
foreach i in 04 10 {
//No IA state election data for 2008
//Drop IA 2000 because doesnt merge with available census map

import delim "$InputPath/Precinct election results/IA_20`i'.tab", clear 
replace state="Iowa"
rename (sd ld) (sldu sldl)


***state specific***

if year==2004{
//Add Linn, Worth, and Wright County turnout
local new = _N + 9
set obs `new'
replace county="Wright" if _n==_N
replace county="Worth" if _n==_N-1
replace county="Linn" if _n<=_N-2 & _n>=_N-8
replace year=2004
replace state="Iowa"

	replace sldl=11 if county=="Worth"
	replace sldu=6 if county=="Worth"
	replace g2004_sth_dv=0 if county=="Worth"
	replace g2004_sth_rv=3108 if county=="Worth"
	replace g2004_sth_tv=3140 if county=="Worth"
	replace g2004_sts_dv=1886 if county=="Worth"
	replace g2004_sts_rv=2161 if county=="Worth"
	replace g2004_sts_tv=4050 if county=="Worth"

	replace sldl=9 if county=="Wright"
	replace g2004_sth_dv=2670 if county=="Wright"
	replace g2004_sth_rv=3545 if county=="Wright"
	replace g2004_sth_tv=6215 if county=="Wright"

	replace sldu=18 if county=="Linn"  & _n==_N-8
	replace g2004_sts_dv=13923 if county=="Linn" & sldu==18
	replace g2004_sts_rv=20686 if county=="Linn" & sldu==18
	replace g2004_sts_tv=34629 if county=="Linn" & sldu==18
	
	replace sldl=29 if  _n==_N-8
	replace sldl=33 if  _n==_N-7
	replace sldl=34 if  _n==_N-6
	replace sldl=35 if  _n==_N-5
	replace sldl=36 if  _n==_N-4
	replace sldl=37 if  _n==_N-3
	replace sldl=38 if  _n==_N-2
	
	replace precinct="1" if  _n==_N-8
	replace precinct="2" if  _n==_N-7
	replace precinct="3" if  _n==_N-6
	replace precinct="4" if  _n==_N-5
	replace precinct="5" if  _n==_N-4
	replace precinct="6" if  _n==_N-3
	replace precinct="7" if  _n==_N-2
	
	replace g2004_sth_dv=6576 if county=="Linn" & sldl==29
	replace g2004_sth_dv=10885 if county=="Linn" & sldl==33
	replace g2004_sth_dv=12306 if county=="Linn" & sldl==34
	replace g2004_sth_dv=8360 if county=="Linn" & sldl==35
	replace g2004_sth_dv=9843 if county=="Linn" & sldl==36
	replace g2004_sth_dv=7493 if county=="Linn" & sldl==37
	replace g2004_sth_dv=8865 if county=="Linn" & sldl==38

	replace g2004_sth_rv=0 if county=="Linn" & sldl==29
	replace g2004_sth_rv=0 if county=="Linn" & sldl==33
	replace g2004_sth_rv=0 if county=="Linn" & sldl==34
	replace g2004_sth_rv=8929 if county=="Linn" & sldl==35
	replace g2004_sth_rv=8220 if county=="Linn" & sldl==36
	replace g2004_sth_rv=9579 if county=="Linn" & sldl==37
	replace g2004_sth_rv=6619 if county=="Linn" & sldl==38

	replace g2004_sth_tv=6699 if county=="Linn" & sldl==29
	replace g2004_sth_tv=11020 if county=="Linn" & sldl==33
	replace g2004_sth_tv=12474 if county=="Linn" & sldl==34
	replace g2004_sth_tv=17300 if county=="Linn" & sldl==35
	replace g2004_sth_tv=18071 if county=="Linn" & sldl==36
	replace g2004_sth_tv=17085 if county=="Linn" & sldl==37
	replace g2004_sth_tv=15501 if county=="Linn" & sldl==38
}

drop if strpos(county,"Total")!=0 //drop totals
drop if strpos(precinct, "Absentee")!=0 //drop absentee votes, reported at county level
do "$CodePath/elections_csld_edits/districts_IA.do" //fix districts, add fips county codes


replace precinct="Oskaloosa Ward 2.2" if precinct=="Oskaloosa Ward 2" & sldl==72
replace precinct="Oskaloosa Ward 3.2" if precinct=="Oskaloosa Ward 3" & sldl==72


********************

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape
save `st', replace

local 04
reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl==. & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu==. & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}

// KANSAS
foreach i in 10 {

// state elections in 2010 only for KS
import delim "$InputPath/Precinct election results/KS_20`i'.tab", clear 
replace state="Kansas"
rename (sd ld) (sldu sldl)

***state specific***
do "$CodePath/elections_csld_edits/districts_KS.do" //add fips county codes, add districts to 2010
replace precinct=vtd
********************

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape
save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl==. & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu==. & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}

// KENTUCKY
foreach i in 10 {

// state elections in 2010 only for ky
import delim "$InputPath/Precinct election results/KY_20`i'.tab", clear 
replace state="Kentucky"
rename (sd ld) (sldu sldl)

***state specific***
drop if precinct=="Total:" | precinct=="Total" | precinct=="AB" | precinct=="PROV" | precinct=="PROVISIONAL 2ND" | precinct=="PROVISIONAL 3RD" | precinct=="County" //Absentee and provisional dropped because assigned to counties only
do "$CodePath/elections_csld_edits/districts_KY.do" //add fips county codes, add districts to 2010

********************

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape
save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl==. & nsd>1

********************
replace sldl=83 if fcounty==53
replace sldl=24 if fcounty==155
replace sldl=97 if fcounty==195 & sldl==95
********************

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu==. & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}


// LOUISIANA
foreach i in  03 07  {
// State elections in odd years for LA

import delim "$InputPath/Precinct election results/LA_20`i'.tab", clear 
replace state="Louisiana"
rename (sd ld) (sldu sldl)

***state specific***
egen precinct1=concat(ward precinct)
replace precinct=precinct1
do "$CodePath/elections_csld_edits/districts_LA.do" //add fips county codes, add districts to 2010
rename parish county

replace sldl=105 if sldl==5 & year==2003 &  fcounty==75 // typos
replace sldl=105 if sldl==5 & year==2003 &  fcounty==51
********************

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape
save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl==. // uper house

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu==. // lower house

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}



// MINNESOTA
foreach i in 10 {

	//Note: 04 has no county codes. A significant share of precincts (206, with 7% of state house votes) have no identifiable county info, because they belong to MCD or fips which span multiple counties (and the precinct name does not reveal the county nor match with 2010 precinct names). Therefore I only use 2010. 

import delim "$InputPath/Precinct election results/MN_20`i'.tab", clear 
replace state="Minnesota"
rename (sd ld_code) (sldu sldl) //MN specific, string sldl

***state specific***

if year==2004  {
gen g20`i'_sts_rv=.
gen g20`i'_sts_dv=.
gen g20`i'_sts_tv=.
}
if year==2010{
gen mcd=""
}

tempfile temp
save `temp'

// Use census gazeteer to merge county names with place fips codes https://www.census.gov/geo/reference/codes/place.html
// note that some places span multiple counties (handled below)
import excel "$InputPath/MN_precinct_county.xls", clear sheet(MN_precinct_county) firstrow

keep fips mcd county_name 
egen tag=tag(fips mcd county_name) 
keep if tag==1
drop if fips==0
drop tag
merge 1:m fips  using `temp'

keep if _m==3
drop _m

do "$CodePath/elections_csld_edits/districts_MN.do" //add fips county codes, add districts to 2010

save `temp', replace 

// use county code crosswalk generated from MN 2010 elections and previous two steps to fill in county fips codes for missing values (missing because place spans multiple counties)
import excel "$InputPath/MN_precinct_county.xls", clear sheet(MN_county_codes) firstrow

merge 1:m county_name using `temp'

*********************

save `st', replace

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)
append using `csldl'
save `csldl', replace

use `st', clear

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}




// MISSOURI 

foreach i in 04 10 {

import delim "$InputPath/Precinct election results/MO_20`i'.tab", clear 
replace state="Missouri"
rename (sd ld) (sldu sldl)

***state specific***
if year==2002{
replace precinct=ward_precinct
tempfile MObase
save `MObase', replace
keep county ward_precinct ward precinct sldl ld*
tempfile MO2002
save `MO2002', replace
use `MObase', clear
}

if year==2004{
	drop sldl ld*
	
	tempfile MO2004
	save `MO2004', replace
	
	import delim "$InputPath/Precinct election results/MO_2002.tab", clear 
	rename (sd ld) (sldu sldl)
	replace precinct=ward_precinct

	keep county ward_precinct ward precinct sldl ld*

merge 1:1 county ward precinct using `MO2004'
egen precinct1=concat(ward precinct)
replace precinct=precinct1

drop if state==""
}


if year==2010{
//drop sldl ld*
//merge 1:1 county precinct using `MO2002'
rename (g2010_sth_tv1) (g2010_sth_tv)
}


do "$CodePath/elections_csld_edits/districts_MO.do" //add fips county codes, add districts to 2010
drop if sldl==. //drops federal and provisional ballots.
****************

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape
save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl==. & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu==. & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}


// MONTANA 
foreach i in 04 10  {

//State elections in 04 08 10 for MT. 2010 does not inlcude district information
import delim "$InputPath/Precinct election results/MT_20`i'.tab", clear 
replace state="Montana"


***state specific***
if year==2004 {
rename (sd ld) (sldu sldl)

}

if year==2010 {

	gen sldl=.
	gen sldu=.
	replace precinct=upper(precinct)
	replace precinct=subinstr(precinct,"PRECINCT","",.)
	replace precinct=subinstr(precinct,"PRECNCT ","",.)
	replace precinct=subinstr(precinct,"#","",.)
	replace precinct=subinstr(precinct,"P ","",.)
	replace precinct=subinstr(precinct,"PCT ","",.)
	replace precinct=subinstr(precinct,"PREC ","",.)
	replace precinct=subinstr(precinct,"NO.  ","",.)
	replace precinct=subinstr(precinct,"CC-","",.)
	replace precinct=subinstr(precinct,"CC","",.)

	replace precinct = usubstr(precinct,-1,.) if real(substr(precinct,-2,1))==. & (county=="Blaine" | county=="Chouteau" | county=="Custer" | county=="Missoula")
	replace precinct = usubstr(precinct,-2,.) if real(precinct)==. & (county=="Blaine" | county=="Chouteau" | county=="Custer" | county=="Missoula")
	replace precinct= usubstr(precinct,1,2) if county=="Carter"
	replace precinct= usubstr(precinct,1,1) if county=="Carter" & substr(precinct,2,1)==" "
	//replace precinct= substr(precinct,1,3) if county=="Phillips"
	//replace precinct = substr(precinct,1,1) if county=="Phillips" & (substr(precinct,-1,.)==" " | substr(precinct,-1,.)=="-")
	replace precinct=substr(precinct,-2,2) if county=="Pondera" | county=="Stillwater"
	replace precinct=substr(precinct,-3,3) if county=="Silver Bow"
	replace precinct=substr(precinct,1,2) if county=="Roosevelt"
	replace precinct=substr(precinct,1,1) if county=="Sheridan"
	replace precinct=substr(precinct,-3,2) if county=="Yellowstone" & real(substr(precinct,-1,1))==.
	replace precinct=substr(precinct,2,2) if county=="Yellowstone" & real(substr(precinct,1,1))==.
	
	replace precinct=substr(precinct,1,2) if county=="Valley" 
	replace precinct=subinstr(precinct,"-","",.) if county=="Valley"
	
	replace precinct = usubstr(precinct, 2, .) if usubstr(precinct,1,1) == "0" | usubstr(precinct,1,1) == " " 
	replace precinct = usubstr(precinct, 2, .) if usubstr(precinct,1,1) == "0" | usubstr(precinct,1,1) == " " 
	replace precinct = usubstr(precinct, 2, .) if usubstr(precinct,1,1) == "0" | usubstr(precinct,1,1) == " " 
	
	
}

do "$CodePath/elections_csld_edits/districts_MT.do" //add fips county codes, add districts to 2010




********************


save `st', replace


collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear


collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}



// NEW JERSEY
foreach i in 03 07  {

// State elections in odd years for NJ

import delim "$InputPath/Precinct election results/NJ_20`i'.tab", clear 
replace state="New Jersey"
rename (sd ld) (sldu sldl)

***state specific***
gen fcounty=substr(geo_code,3,3)
destring fcounty, replace
egen precincttown=concat(precinct town ward)
rename precinct precinct1
gen precinct=precincttown

foreach j in dv rv tv { 
replace g20`i'_sth_`j'=g20`i'_sth2_`j'+g20`i'_sth_`j' //sum the votes for 2 members in each district
if year==2005 {
gen g2005_sts_`j'=.
}
}


********************

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape
save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd) //NJ SPECIFIC
rename ld sldl
drop if sldl==. & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd) //MJ SPECIFIC
rename sd sldu
drop if sldu==. & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}


// NORTH CAROLINA
foreach i in  04 10 {

import delim "$InputPath/Precinct election results/NC_20`i'.tab", clear 
replace state="North Carolina"
rename (sd ld) (sldu sldl)

***state specific***
if year==2010{
rename vtd precinct
replace county=upper(county)
}

do "$CodePath/elections_csld_edits/districts_NC.do" //add fips county codes


if year==2006{
replace precinct="CLINTON 1" if precinct[_n-1]=="CLEMENT"   & county=="SAMPSON" //duplicate precinct names, relabel before reshape
replace precinct="CLINTON 2" if precinct[_n-1]=="CLINTON 1" & county=="SAMPSON"
replace precinct="CLINTON 3" if precinct[_n-1]=="CLINTON 2" & county=="SAMPSON"
replace precinct="CLINTON 4" if precinct[_n-1]=="CLINTON 3" & county=="SAMPSON"
replace precinct="CLINTON 5" if precinct[_n-1]=="CLINTON 4" & county=="SAMPSON"
}


********************

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape
save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd) 
rename ld sldl
drop if sldl==. & nsd>1

**** MORE STATE SPECIFIC NC ONLY***
replace sldl=108 if precinct=="12" & fcounty==71 
replace sldl=66 if precinct=="UWH" & fcounty==123
replace sldl=67 if precinct=="ELD" & fcounty==123
replace sldl=50 if fcounty==135 & sldl==4
********************

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd) 
rename sd sldu
drop if sldu==. & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}


// OHIO  
foreach i in 10 {
//state elections for 2010 only in OH
import delim "$InputPath/Precinct election results/OH_20`i'.tab", clear 
replace state="Ohio"
rename (sd ld) (sldu sldl)

***state specific***
do "$CodePath/elections_csld_edits/districts_OH.do" //add fips county codes
sort year county precinct
drop if county[_n]==county[_n-1] & precinct[_n]==precinct[_n-1] //fix error: Carroll county listed twice
drop if precinct=="FEDERAL ONLY" | precinct=="FEDERAL" 
********************

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape
save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl==. & nsd>1

********************
replace sldl=4 if county=="Allen"
********************

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu==. & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}

// OKLAHOMA 
foreach i in 10 {
// State election for OK in 2010 only

import delim "$InputPath/Precinct election results/OK_20`i'.tab", clear 
replace state="Oklahoma"
rename (sd ld) (sldu sldl)

***state specific***
split precinct, generate(county) parse(" CO. ")
rename county1 county
do "$CodePath/elections_csld_edits/districts_OK.do" //add fips county codes

bysort county sldl: gen sldlflag=_n==1
egen ndistrict=total(sldlflag), by(county)
egen sldlavg=mean(sldl), by(county)
replace sldl=sldlavg if ndistrict==1
foreach j in d r t {
replace g2010_sth_`j'v=g2010_vote_`j'v+g2010_sth_`j'v //add straight ticket votes
}
********************

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape
save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl==. & nsd>1

********************
do "$CodePath/elections_csld_edits/districts_OK2.do" //sldl errors
********************

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu==. & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}


// OREGON
foreach i in 10 {

// State election for OR in 2010 only

import delim "$InputPath/Precinct election results/OR_20`i'.tab", clear 
replace state="Oregon"
rename (sd ld) (sldu sldl)

***state specific***
do "$CodePath/elections_csld_edits/districts_OR.do" //add fips county codes
replace precinct=precinct_name if precinct==""
drop if sldl==. //only removes observations with no sts or sth votes
********************

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape
save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl==. & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu==. & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}

// PENNSYLVANNIA 
foreach i in 2004 2010 {

import delim "$InputPath/Precinct election results/PA_`i'.tab", clear 
replace state="Pennsylvania"
rename (fips sd ld) (fcounty sldu sldl)

***state-specific***
replace sldl=148 if fcounty==91 & sldl==46 
replace sldu=1 if fcounty==101 & vtd=="560" //typo: sldu=1, not 10  (philly vtd)
********************

save `st', replace


collapse (sum) g`i'_sth_dv g`i'_sth_rv g`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear


collapse (sum) g`i'_sts_dv g`i'_sts_rv g`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}
// South Carolina --sldl only
foreach i in 10 {

import delim "$InputPath/Precinct election results/SC_20`i'.tab", clear 
replace state="South Carolina"

rename (ld) ( sldl) 

***state specific***
replace county="Beaufort" if county=="Bearfort"
replace county="Chesterfield" if county=="Chsterfield"
replace county="Dillon" if county=="Dillion"
replace county="Marlboro" if county=="Malrboro"
replace county="Orangeburg" if county=="Oragneburg"
replace county="Sumter" if county=="Sumpter"
replace sldl=81 if county=="Aiken" & sldl==.
do "$CodePath/elections_csld_edits/districts_SC.do" //add fips county codes
********************

rename (sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv ) ( ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 ) //prepare for reshape
save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl==. & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

}


// TENNESSEE 
foreach i in 04 10 {

import delim "$InputPath/Precinct election results/TN_20`i'.tab", clear 
replace state="Tennessee"
rename (sd ld) (sldu sldl)

***state specific***
do "$CodePath/elections_csld_edits/districts_TN.do" //add fips county codes
drop if precinct=="Early Voting" & sldl==.
********************

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape
save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl==. & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu==. & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}

// TEXAS 
foreach i in  2002 2010 {


import delim "$InputPath/Precinct election results/TX_`i'.tab", clear 
replace state="Texas"
rename (sd ld) (sldu sldl)

***state specific***
rename (fips vtd) (fcounty precinct)
********************

save `st', replace

collapse (sum) g`i'_sth_dv g`i'_sth_rv g`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

collapse (sum) g`i'_sts_dv g`i'_sts_rv g`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}


// WISCONSIN 

foreach i in 04 10 {

//foreach i in  04 10 {
import delim "$InputPath/Precinct election results/WI_20`i'.tab", clear 
replace state="Wisconsin"
rename (sd ld) (sldu sldl)

***state specific***
rename (fips_cnty) (fcounty)
replace fcounty=fcounty-55000
do "$CodePath/elections_csld_edits/districts_WI.do" //add fips county codes
********************
save `st', replace

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}

// WYOMING 
foreach i in 10 {
// State election for WY in 2010 only

import delim "$InputPath/Precinct election results/WY_20`i'.tab", clear 
replace state="Wyoming"
rename (sd ld) (sldu sldl)

***state specific***
replace precinct=upper(precinct) if county=="Freemont"  //create unique precinct name for mispelt fremont county entries
replace county="Fremont" if county=="Freemont"
replace county="Uinta" if county=="Uinita"
replace sldl=36 if county=="Natrona" & precinct=="Kelly Walsh High School 3-5" //add missing district info
replace sldl=sldu if county=="Albany" //sldl and sldu info were swapped
do "$CodePath/elections_csld_edits/districts_WY.do" //add fips county codes
********************

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape
save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl==. & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu==. & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace
}


// Collapse again so that 2002 and 2004 become 2007; 2008 and 2010 become 2012
use `csldl', clear

rename year electionyear
gen year=.
replace year=2012 if electionyear>2004
replace year=2007 if electionyear<=2004 //use 2004 election year as closest to 2006-07 fiscal year


foreach i of varlist g* {
gen allmissing_`i'=0
replace allmissing_`i'=1 if `i'==.
}

collapse (sum) g* (mean) allmissing*, by(year state fcounty sldl)

foreach i of varlist g* {
replace `i'=. if allmissing_`i'==1
}

drop allmissing*

destring sldl, replace
drop if sldl==.

statastates, name(state) 
drop if _m!=3 
drop _m
replace state=proper(state)
rename state_fips fstate 

cd "$OutputPath"
save elections_csldl, replace

use `csldu', clear
rename year electionyear
gen year=.
replace year=2012 if electionyear>2004 & electionyear!=.
replace year=2007 if electionyear<=2004 //use 2004 election year as closest to 2006-07 fiscal year


foreach i of varlist g* {
gen allmissing_`i'=1 if `i'==.
}

collapse (sum) g* (mean) allmissing*, by(year state fcounty sldu)

foreach i of varlist g* {
replace `i'=. if allmissing_`i'==1
}

drop allmissing*

destring sldu, replace
drop if sldu==.

statastates, name(state) 
drop if _m!=3 
drop _m
replace state=proper(state)
rename state_fips fstate 

cd "$OutputPath"
save elections_csldu, replace

